CREATE TABLE [dbo].[Name_Security]
(
[ID] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_Name_Security_ID] DEFAULT (''),
[LOGIN_DISABLED] [bit] NOT NULL CONSTRAINT [DF_Name_Security_LOGIN_DISABLED] DEFAULT ((0)),
[WEB_LOGIN] [varchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_Name_Security_WEB_LOGIN] DEFAULT (''),
[PASSWORD] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_Name_Security_PASSWORD] DEFAULT (''),
[EXPIRATION_DATE] [datetime] NULL,
[LAST_LOGIN] [datetime] NULL,
[PREVIOUS_LOGIN] [datetime] NULL,
[ContactID] [int] NOT NULL IDENTITY(1, 1),
[UPDATED_BY] [varchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_Name_Security_UPDATED_BY] DEFAULT (''),
[TIME_STAMP] [timestamp] NULL
) ON [PRIMARY]

GO

CREATE TRIGGER [dbo].[asi_Name_Security_Delete]
    ON [dbo].[Name_Security]
    FOR DELETE
AS
BEGIN
    SET NOCOUNT ON
    UPDATE um
       SET um.[IsDisabled] = 1,
           um.[UserId] = ''
      FROM deleted d
           INNER JOIN [dbo].[UserMain] um ON d.[WEB_LOGIN] = um.[UserId]
     WHERE d.[WEB_LOGIN] <> ''
    SET NOCOUNT OFF
END


GO
CREATE TRIGGER [dbo].[asi_Name_Security_Update]
   ON  [dbo].[Name_Security]
   FOR UPDATE
AS
BEGIN
    IF EXISTS (SELECT 1 FROM inserted i JOIN deleted d ON i.ID = d.ID
                WHERE i.[LOGIN_DISABLED] <> d.[LOGIN_DISABLED] OR i.[EXPIRATION_DATE] <> d.[EXPIRATION_DATE]
                      OR (i.[EXPIRATION_DATE] IS NULL AND d.[EXPIRATION_DATE] IS NOT NULL)
                      OR (i.[EXPIRATION_DATE] IS NOT NULL AND d.[EXPIRATION_DATE] IS NULL))
    BEGIN
        UPDATE u
           SET u.[ExpirationDate] = i.[EXPIRATION_DATE],
               u.[IsDisabled] = i.[LOGIN_DISABLED],
               u.[UpdatedOn] = GETDATE()
          FROM [dbo].[UserMain] u INNER JOIN inserted i ON u.[ContactMaster] = i.[ID]
    END    
END

GO
ALTER TABLE [dbo].[Name_Security] ADD CONSTRAINT [PK_Name_Security] PRIMARY KEY NONCLUSTERED ([ID]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [iName_SecurityContactID] ON [dbo].[Name_Security] ([ContactID]) ON [PRIMARY]
GO
CREATE CLUSTERED INDEX [iName_SecurityLOGIN] ON [dbo].[Name_Security] ([WEB_LOGIN]) ON [PRIMARY]
GO
GRANT REFERENCES ON  [dbo].[Name_Security] TO [IMIS]
GRANT SELECT ON  [dbo].[Name_Security] TO [IMIS]
GRANT INSERT ON  [dbo].[Name_Security] TO [IMIS]
GRANT DELETE ON  [dbo].[Name_Security] TO [IMIS]
GRANT UPDATE ON  [dbo].[Name_Security] TO [IMIS]
GO